/*******************************************************************************
 * Dreampalace
 *******************************************************************************
 * 
 * Dreampalace is a special dreamdiary which helps dreamers to understand their
 * dreams. You can find also many features in Dreampalace to learn and hold the
 * lucid state in your dreams.
 * 
 * *****************************************************************************
 *
 * Program	   : Dreampalace
 * Autor           : PRo (Peter Rogge)
 * Email           : rogge-peter@gmx.de
 * Homepage        : http://code.google.com/p/dreampalace
 * License	   : GNU General Public License 3.0
 * 
 *******************************************************************************
 * 
 * This file is part of the project Dreampalace. Dreampalace is free software:
 * you can redistribute it and/or modify it under the terms of the GNU General 
 * Public License as published by the Free Software Foundation in version 3 of
 * the License only.
 * 
 * Dreampalace is distributed in the hope that it will be useful, but WITHOUT 
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * Dreampalace. If not, see <http://www.gnu.org/licenses/>.
 * 
 * The Dreampalace project designates this particular file as subject to the
 * "Classpath" exception as provided by the Dreampalace project in the GPL 
 * Version 3 section of the License file that accompanied this code.
 * 
 *******************************************************************************
 */
package de.pro.dreampalace.file.links;

import com.zenjava.jfxflow.actvity.Activity;
import com.zenjava.jfxflow.actvity.FxmlLoader;
import com.zenjava.jfxflow.navigation.NavigationManager;
import de.pro.dreampalace.database.api.IDatabaseManager;
import de.pro.dreampalace.file.api.EFileTyp;
import de.pro.dreampalace.file.links.api.ILinksManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Logger;
import org.openide.util.Lookup;
import org.openide.util.NbBundle;
import org.openide.util.lookup.ServiceProvider;

/**
 * TODO
 * update version
 * 
 * Next steps
 *  - gui for
 *     - Links.fxml
 *     - LinksEditor.fxml
 *  - Basic-functionality for
 *     - create, delete, edit category, link
 *     - links without a category aren't shown anywhere
 * 
 * Gui Links
 *  - Use ContextMenu for new, edit, delete categories and links
 *     - So I don't need the LinksEditor stuff
 *  - Root = ScrollPane
 *     - Menu: New Category
 *  - Every Category is a VBox
 *     - Menu: New Category, Edit Category, Delete Category | Up, Down | New Link
 *  - Every Link is added to the VBox
 *     - Menu: New Link, Edit Link, Delete Link | Up, Down
 * 
 * 
 * Table
 *  - FileLinksCategories
 *     - id, name, isorder, ordernr
 *  - FileLinks
 *     - id, name, url, date, description, isextern, favorite, isorder, ordernr, category
 *     - idea: documentart: html or file
 * 
 * Parameter
 *  - IsExtern / 0=intern, 1=extern(browser)
 *  - IsOrder / 0=no, 1=asc, 2=desc, 3=own (order)
 *  - Favorite / 0=no, 1-5=yes
 *
 * @author Peter Rogge (PRo) <rogge-peter@gmx.de>
 */
@ServiceProvider(service=ILinksManager.class)
public final class LinksManager implements ILinksManager {

    private Connection connection = null;
    private NavigationManager navigationManager = null;
    
    @Override
    public final void checkTables() {
        System.out.println("LinksManager.checkTables()");

        this.checkTableFileLinksCategories();
        this.checkTableFileLinks();
    }
    
    private void checkTableFileLinksCategories() {
        // Check if the table exists...
        final IDatabaseManager dm = Lookup.getDefault().lookup(
                IDatabaseManager.class);
        final boolean isCreated = dm.checkTable("FileLinksCategories"); // NOI18N
        if (isCreated) {
            Logger.getLogger(LinksManager.class.getName()).info(
                "Table FILE_LINKS_CATEGORIES is created."); // NOI18N
            return;
        }
        
        // Otherwise create the table
        this.createTableFileLinksCategories();
    }
    
    private void createTableFileLinksCategories() {
        Logger.getLogger(LinksManager.class.getName()).info(
            "Create table FILE_LINKS_CATEGORIES ..."); // NOI18N
        
        final StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE FileLinksCategories ("); // NOI18N
            sql.append("ID BIGINT NOT NULL, "); // NOI18N
            sql.append("Name VARCHAR(64) NOT NULL, "); // NOI18N
            sql.append("IsOrder INT NOT NULL, "); // NOI18N
            sql.append("OrderNr INT NOT NULL, "); // NOI18N
        sql.append("PRIMARY KEY(ID))"); // NOI18N
        
        final IDatabaseManager dm = Lookup.getDefault().lookup(IDatabaseManager.class);
        try {
            dm.createTable(sql.toString());
            
            Logger.getLogger(LinksManager.class.getName()).info(
                "Table FILE_LINKS_CATEGORIES is created."); // NOI18N
        } catch (SQLException ex) {
            Logger.getLogger(LinksManager.class.getName()).warning(
                "Can't create the table FILE_LINKS_CATEGORIES:"); // NOI18N
            Logger.getLogger(LinksManager.class.getName()).warning(
                ex.getMessage());
        }
    }
    
    private void checkTableFileLinks() {
        
        // Check if the table exists...
        final IDatabaseManager dm = Lookup.getDefault().lookup(
                IDatabaseManager.class);
        final boolean isCreated = dm.checkTable("FileLinks"); // NOI18N
        if (isCreated) {
            Logger.getLogger(LinksManager.class.getName()).info(
                "Table FILE_LINKS is created."); // NOI18N
            return;
        }
        
        // Otherwise create the table
        this.createTableFileLinks();
    }
    
    private void createTableFileLinks() {
        Logger.getLogger(LinksManager.class.getName()).info(
            "Create table FILE_LINKS ..."); // NOI18N
        
        final StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE FileLinks ("); // NOI18N
            sql.append("ID BIGINT NOT NULL, "); // NOI18N
            sql.append("Name VARCHAR(128) NOT NULL, "); // NOI18N
            sql.append("URL VARCHAR(512) NOT NULL, "); // NOI18N
            sql.append("Date DATE NOT NULL, "); // NOI18N
            sql.append("Description VARCHAR(1024) NOT NULL, "); // NOI18N
            sql.append("IsExtern INT NOT NULL, "); // NOI18N
            sql.append("Favorite INT NOT NULL, "); // NOI18N
            sql.append("IsOrder INT NOT NULL, "); // NOI18N
            sql.append("OrderNr INT NOT NULL, "); // NOI18N
            sql.append("Category BIGINT NOT NULL, "); // NOI18N
        sql.append("PRIMARY KEY(ID))"); // NOI18N
        
        final IDatabaseManager dm = Lookup.getDefault().lookup(IDatabaseManager.class);
        try {
            dm.createTable(sql.toString());
            
            Logger.getLogger(LinksManager.class.getName()).info(
                "Table FILE_LINKS is created."); // NOI18N
        } catch (SQLException ex) {
            Logger.getLogger(LinksManager.class.getName()).warning(
                "Can't create the table FILE_LINKS:"); // NOI18N
            Logger.getLogger(LinksManager.class.getName()).warning(
                ex.getMessage());
        }
    }

    @Override
    public Activity getActivity(EFileTyp fileTyp) {
        final FxmlLoader loader = new FxmlLoader();
        
        return loader.load(fileTyp.getPlace(), NbBundle.getBundle(
                        LinksActivity.class));
    }

    @Override
    public NavigationManager getNavigationManager() {
        return navigationManager;
    }
    
    @Override
    public final void setConnection(final Connection connection) {
        this.connection = connection;
    }

    @Override
    public void setNavigationManager(NavigationManager navigationManager) {
        this.navigationManager = navigationManager;
    }
}

//    private String loadFileNotes() {
//        final ResultSet result = this.loadFileNotesFromDatabase();
//        String notes = null;
//        try {
//            result.next();
//            
//            final IUtilManager um = Lookup.getDefault().lookup(IUtilManager.class);
//            notes = um.getTextFromClob(result.getClob(1));
//        } catch (SQLException ex) {
//            notes = NbBundle.getMessage(this.getClass(), "notes.initial"); // NOI18N
//            this.saveNewFileNotes(notes);
//        } finally {
//            if (result != null) {
//                try {
//                    result.close();
//                } catch (SQLException ex) { }
//            }
//        }
//        
//        return notes;
//    }
    
//    private ResultSet loadFileNotesFromDatabase() {
//        PreparedStatement ps = null;
//        ResultSet result = null;
//        
//        try {
//            ps = connection.prepareStatement(
//                    "SELECT Content " // NOI18N
//                    + "FROM File " // NOI18N
//                    + "WHERE ID=?"); // NOI18N
//            ps.setLong(1, EFileTyp.NOTES.getID());
//            
//            result = ps.executeQuery();
//        } catch (SQLException ex) {
//            return null;
//        } finally {
//            if (ps != null) {
//                try {
//                    ps.close();
//                } catch (SQLException ex) { }
//            }
//        }
//
//        return result;
//    }

//    private void saveNewFileNotes(final String notes) {
//        Logger.getLogger(NotesActivity.class.getName()).info(
//                "Save file NOTES first time :)"); // NOI18N
//        
//        PreparedStatement ps = null;
//        
//        try {
//            ps = connection.prepareStatement(
//                    "INSERT INTO File(" // NOI18N
//                        + "ID, Title," // NOI18N
//                        + "Date, Content, " // NOI18N
//                        + "FileTyp" + ") " // NOI18N
//                    + "VALUES(?, ?, ?, ?, ?)"); // NOI18N
//
//            ps.setLong(1, EFileTyp.NOTES.getID());
//            ps.setString(2, "Notes"); // NOI18N
//            ps.setDate(3, new java.sql.Date(System.currentTimeMillis()));
//            ps.setClob(4, this.getClob(notes));
//            ps.setString(5, EFileTyp.NOTES.toString());
//
//            ps.execute();
//        } catch (SQLException ex) {
//            Logger.getLogger(NotesActivity.class.getName()).warning(
//                "Can't insert the file NOTES:"); // NOI18N
//            Logger.getLogger(NotesActivity.class.getName()).warning(
//                ex.getMessage());
//        } finally {
//            try {
//                if(ps != null) { ps.close(); }
//            } catch (SQLException ex) { }
//        }
//    }
    
//    private void updateFileNotes() {
//        Logger.getLogger(NotesActivity.class.getName()).info(
//                "File NOTES is changed --> so save it"); // NOI18N
//        
//        PreparedStatement ps = null;
//        
//        try {
//            ps = connection.prepareStatement(
//                    "UPDATE File " // NOI18N
//                    + "SET Content=? " // NOI18N
//                    + "WHERE ID=?"); // NOI18N
//
//            ps.setClob(1, this.getClob(htmlEditor.getHtmlText()));
//            ps.setLong(2, EFileTyp.NOTES.getID());
//
//            ps.execute();
//        } catch (SQLException ex) {
//            Logger.getLogger(NotesActivity.class.getName()).warning(
//                "Can't update the file NOTES:"); // NOI18N
//            Logger.getLogger(NotesActivity.class.getName()).warning(
//                ex.getMessage());
//        } finally {
//            try {
//                if(ps != null) { ps.close(); }
//            } catch (SQLException ex) { }
//        }
//    }
    
//    private Clob getClob(final String text) throws SQLException {
//        final Clob clob = connection.createClob();
//        clob.setString(1, text);
//
//        return clob;
//    }